package com.vehiclemanagement.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.vehiclemanagement.entity.FuelRefill;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 加油记录Mapper接口
 */
@Mapper
public interface FuelRefillMapper extends BaseMapper<FuelRefill> {
    
    /**
     * 自定义分页查询，带关联信息
     * 
     * @param page 分页对象
     * @param whereSql 自定义WHERE条件SQL（不包含WHERE关键字）
     * @return 分页结果
     */
    @Select("SELECT f.*, v.plate_number, d.name as driver_name, fc.card_number as fuel_card_number, " +
            "CASE f.fuel_type " +
            "WHEN 1 THEN '92号汽油' " +
            "WHEN 2 THEN '95号汽油' " +
            "WHEN 3 THEN '98号汽油' " +
            "WHEN 4 THEN '0号柴油' " +
            "ELSE '其他' END as fuel_type_name " +
            "FROM fuel_refill f " +
            "LEFT JOIN vehicle v ON f.vehicle_id = v.id " +
            "LEFT JOIN driver d ON f.driver_id = d.id " +
            "LEFT JOIN fuel_card fc ON f.fuel_card_id = fc.id " +
            "WHERE ${whereSql}")
    IPage<FuelRefill> selectPageWithInfo(Page<FuelRefill> page, @Param("whereSql") String whereSql);

    /**
     * 统计某辆车的总加油量
     * 
     * @param vehicleId 车辆ID
     * @return 总加油量
     */
    @Select("SELECT COALESCE(SUM(fuel_amount), 0) FROM fuel_refill WHERE vehicle_id = #{vehicleId} AND del_flag = 0")
    BigDecimal sumFuelAmountByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 统计某辆车的总加油金额
     * 
     * @param vehicleId 车辆ID
     * @return 总加油金额
     */
    @Select("SELECT COALESCE(SUM(total_price), 0) FROM fuel_refill WHERE vehicle_id = #{vehicleId} AND del_flag = 0")
    BigDecimal sumTotalPriceByVehicleId(@Param("vehicleId") Long vehicleId);

    /**
     * 统计月度加油费用
     * 
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @return 月度加油费用统计
     */
    @Select("SELECT DATE_FORMAT(refill_time, '%Y-%m') as month, SUM(total_price) as totalPrice " +
            "FROM fuel_refill " +
            "WHERE refill_time BETWEEN #{startTime} AND #{endTime} AND del_flag = 0 " +
            "GROUP BY DATE_FORMAT(refill_time, '%Y-%m') " +
            "ORDER BY month")
    List<Map<String, Object>> statisticsByMonth(@Param("startTime") LocalDateTime startTime, @Param("endTime") LocalDateTime endTime);

    /**
     * 物理删除记录
     * 
     * @param id 记录ID
     * @return 影响行数
     */
    @Delete("DELETE FROM fuel_refill WHERE id = #{id}")
    int deletePhysically(@Param("id") Long id);
} 